<?php
/**
*  @package :  class.main
*  @classID :  DB.class.php
*  @author  :  邝达峰
*  @date    : 2017.07.12
*  数据库扩展类
*/

#判断adodb插件是否存在
if (file_exists(SYSTEM_LIB_PATH.'adodb'.DIRECTORY_SEPARATOR.'adodb.inc.php'))
{
	include_once SYSTEM_LIB_PATH.'adodb'.DIRECTORY_SEPARATOR.'adodb.inc.php';
}
else
{
	throwException($GLOBALS['lang']['INCLUDE_ADODB_NOT_EXISTS']);
}

class DB
{
	/**
	*  唯一实例
	*  @static
	*  @var object
	*  @access private
	*/

	//选择主从
	public static $_func = '';
	//默认数据库
	private static $sel_db = 'net114';
	//最后插入的id
	private static $insert_id = null;
	//默认配置链接，default为主数据库
	public static $db_connect = 'default';
	//是否输出404错误
	public static $is_to_404 = true;
	//页面最后操作的查询语句
	private static $last_query = array();
	//是否显示最后执行语句
	private static $is_show_last_query = false;
	//数据库配置
	private static $db_config = array();
	//选择数据库仓库
	private static $sel_storage = '';
	//是否强制使用主数据库
	private static $is_use_master = '';


	/**
	*  构造函数
	* @param:  NULL
    * @return: NULL
    * @access: public
    */
	private function __construct() {

	}


	/**
	* 析构函数
	* @param:  NULL
	* @return: NULL
	* @access: public
	*/
	public function __destruct(){
		#关闭数据库
		self::close();
	}


	/**
	 * 设置数据库相关配置
	 * @return void
	 */
	private  static function setConfig()
	{
		#获取数据库配置
		self::$db_config = Config::get('db');
		self::$is_show_last_query = self::$db_config['is_export_exe_sql'];
		if(!empty(self::$db_config[self::$sel_db]) && is_array(self::$db_config[self::$sel_db])){
			self::$db_config = self::$db_config[self::$sel_db];
		}
	}

	/**
	*  获取到数据库唯一对象
	*  @param:  NULL
	*  $db ref
	*  @access: public
	*  @static
		可自主选择数据库连接使用如下：
		DB::getInstance('cc')->select($sql...) 与 参数与DB::select() 一致 
		DB::getInstance('cc')->insert(...) 与 参数与DB::insert() 一致 
		DB::getInstance('cc')->update(...) 与 参数与DB::update() 一致 
		DB::getInstance('cc')->delete(...) 与 参数与DB::delete() 一致 
		其中'cc'为db_conf.php中设置数据库链接．一般情况下不要使用此功能
		g为getInstance别名
	*/
	public static function g($db_name='')
	{
		return self::getInstance($db_name);
	}

	/**
	*  唯一实例
	*  @static
	*  @param  $db_name 数据库名称
	*  @param  $is_master 是否强制使用主数据库
	*  @access private
	*/
	public static function getInstance($db_name='', $is_master = false)
	{
		static $instance;
		if(!empty($db_name)){
			if($db_name=='insert'){
				$db_name = self::$sel_db;
				$is_master = true;
			}
			self::$sel_storage = $db_name;
			self::$is_use_master = $is_master;
		}
		//获取数据库操作对象
		if(!isset($instance))
		{
			$c = __CLASS__;
			$instance = new $c;
		}
		return $instance;
	}

	/**
	*  获取到数据库连接
	*  @static
	*  @var object
	*  @access private
	*/
	public static function &getConnection($db_name,$isReConn=false)
	{
		return self::getInstance()->getDataBase($db_name,$isReConn);
	}

	/**
	 * 设置mysql连接参数
	 * @param array $db_conf 数据库配置
	 */
	private function getDsn($db_conf)
	{
		#判断数据库配置是否存在
		if(!is_array($db_conf)){
			throwException($GLOBALS['lang']['ADODB_CONF_NOT_EXISTS']);
		}
		#处理数据库设置
		$db_dsn = "{$db_conf['driver']}://{$db_conf['user']}:{$db_conf['pass']}@{$db_conf['host']}:{$db_conf['port']}/{$db_conf['db_name']}";
		$query = array();
		#判断数据库是否永久连接
		if ($db_conf['persist']) {
			$query[] = 'persist';
		}
		#判断数据库是否打开调试开关
		if ($db_conf['debug']) {
			$query[] = 'debug';
		}
		#获取模式
		if ($db_conf['fetchmode'])
		{
			switch ($db_conf['fetchmode'])
			{
				case 'num':$query[] = 'fetchmode=' . ADODB_FETCH_NUM;break;
				case 'assoc':$query[] = 'fetchmode=' . ADODB_FETCH_ASSOC;break;
				case 'both':$query[] = 'fetchmode=' . ADODB_FETCH_BOTH;break;
			}
		}
		#处理查询语句
		if ($query){
			$db_dsn .= '?' . implode('&', $query);
		}
		return $db_dsn;
	}

	/**
	*  获取数据库连接
	*  @param:  NULL
	*  @return: $db ref
	*  @access: public
	*/
	public function &getDataBase($db_name,$isReConn=false)
	{
		/**获取数据库配置**/
		self::$db_config = Config::get('db');
		self::$is_show_last_query = self::$db_config['is_export_exe_sql'];
		$database_name = self::$sel_storage?self::$sel_storage:self::$sel_db;
		self::$db_config = self::$db_config[$database_name];
		if (!in_array(self::$_func,array('insert','update'))){
			self::$sel_storage = '';
		}
		/**获取数据库配置**/

		static $dbconn = array();
		if($db_name == -1) {
			return $dbconn;
		}

		if(empty(self::$db_config[$db_name]) || self::$db_connect != 'default') {
			$db_name = self::$db_connect;
		}

		#判断是否强制使用主数据库
		if(self::$is_use_master){
			$db_name = 'default';
		}

		if ($dbconn[$db_name] && is_object($dbconn[$db_name]) && ($dbconn[$db_name] == self::$sel_db))
		{
			if($isReConn) {
				$dbconn[$db_name]->close();
			}
			else {
				return $dbconn[$db_name];
			}
		}

		#数据库主机
		if(self::$db_config[$db_name]['host'])
		{
			$db_conf = self::$db_config[$db_name];
			$is_arr_db_conf = false;
		}
		else
		{
			if(is_array(self::$db_config[$db_name])){
				#随机选择数据库
				$db_conf =self::$db_config[$db_name][array_rand(self::$db_config[$db_name])];
			}else
			{
				$db_conf =self::$db_config[$db_name][0];
			}
			$is_arr_db_conf = true;
		}

		#设置mysql连接参数
		$db_dsn = $this->getDsn($db_conf);
		if (!($dbconn[$db_name] = &NewADOConnection($db_dsn)))
		{
			if($is_arr_db_conf)
			{
				foreach (self::$db_config[$db_name] as $db_conf)
				{
					usleep(200000);
					$db_dsn = $this->getDsn($db_conf);
					if ($dbconn[$db_name] = &NewADOConnection($db_dsn)) {
						break;
					}
				}
			}
			else
			{
				usleep(200000);
				$dbconn[$db_name] = &NewADOConnection($db_dsn);
			}
			if(!$dbconn[$db_name]) {
				#错误抛出
				throwException($GLOBALS['lang']['ADODB_NOT_CONNECT'].'['.mysql_errno().'] '.':'.$db_dsn);
			}
		}
		#设定字符集空间
		if ($db_conf['charset'])
		{
			$dbconn[$db_name]->Execute("SET NAMES '{$db_conf['charset']}'"); //aodb_lite
			if($db_name == 'select') {
				$dbconn[$db_name]->Execute("set @@wait_timeout = 2");
			}
			else{
				$dbconn[$db_name]->Execute("set @@wait_timeout = 10");
			}
		}
		return $dbconn[$db_name];
	}

	/**
	 * 关闭数据连接
	 */
	public static function close()
	{
		$arrDbconn = &DB::getConnection(-1);
		if($arrDbconn)
		{
			foreach ($arrDbconn as $v)
			{
				$v->close();
			}
		}
	}

	/**
	 * 获取数据表列信息
	 * @param string $tbl 表名
	 * @param object $dbconn 数据库操作对象
	 * @return object
	 */
	private function tblColumns($tbl,& $dbconn)
	{
		static $columns;
		if(!$columns[$tbl]){
			$columns[$tbl] = $dbconn->MetaColumns($tbl);
		}
		return $columns[$tbl];
	}


	/**
	 * 生成插入数据库SQL
	 * @param string $tbl 表名 
	 * @param string $defaultfields 默认字段 
	 */
	public function buildInsert($tbl, $defaultfields = '') {

		$result = false;
		if (!is_array($defaultfields))
		{
			$defaultfields = $_REQUEST;
			if (!is_array($defaultfields))
			{
				throwException($GLOBALS['lang']['SQL_INSERT_FIELDS_EMPTY']);
			}
		}
		if(!($dbconn = &DB::getConnection(DB::$_func))) {
			throwException($GLOBALS['lang']['ADODB_NOT_CONNECT']);
		}
		#获取到表的列信息
		$info = $this->tblColumns($tbl,$dbconn);
		if (!is_array($info))
		{
			throwException($GLOBALS['lang']['SQL_INSERT_TABLE_ERROR'].':'.$tbl);
		}
		#判断表的列信息是否准确
		foreach ($info as $cols) {
			if (array_key_exists($cols->name, $defaultfields)) {
				$fields[]= "`".$cols->name."`";
				$values[]= self::filtrateValue($defaultfields[$cols->name]);
			}
			elseif($cols->auto_increment != 1 && (($cols->not_null && !$cols->has_default) || $cols->primary_key)){
				throwException($tbl.$GLOBALS['lang']['SQL_INSERT_FIELD_NOEMPTY'].':'.$cols->name);
			}
		}
		$fields = implode(',',$fields);
		$values = implode(',',$values);
		#组成插入语句
		$result = "INSERT INTO $tbl ($fields) VALUES ($values)";
		return $result;
	}

	/**
	 * 生成删除语句
	 * @param string $tbl 表名 
	 * @param string $where 查询条件 
	 */
	public function buildDelete($tbl, $where = '')
	{
		if(empty($tbl))
		{
			throwException($GLOBALS['lang']['SQL_DEL_TABLE_ERROR']);
		}
		if(empty($where))
		{
			throwException($GLOBALS['lang']['SQL_WHERE_EMPTY']);
		}
		#组成删除语句
		$sql = "delete from $tbl where $where";
		return $sql;
	}

	/**
	 * 生成更新语句
	 * @param string $tbl 表名 
	 * @param string $wherefields 查询条件 
	 * @param string $defaultfields 默认字段 
	 */
	public function buildUpdate($tbl, $wherefields = '',$defaultfields = '')
	{
		static $tblColumns;
		if(empty($wherefields))
		{
			throwException($GLOBALS['lang']['SQL_WHERE_EMPTY']);
		}
		if (!is_array($defaultfields))
		{
			$defaultfields = $_REQUEST;
			if (!is_array($defaultfields))
			{
				throwException($GLOBALS['lang']['SQL_UPD_FIELDS_EMPTY']);
			}
		}
		$result = false;
		if(!($dbconn = &DB::getConnection(DB::$_func))) {
			throwException($GLOBALS['lang']['ADODB_NOT_CONNECT']);
		}
		#获取到表的列信息
		$info = $this->tblColumns($tbl,$dbconn);
		if (!is_array($info))
		{
			throwException($GLOBALS['lang']['SQL_UP_TABLE_ERROR']);
		}
		#判断表的列信息是否准确
		foreach ($info as $cols) {
			if (array_key_exists($cols->name, $defaultfields)) { //isset($defaultfields["$cols->name"])
				if($cols->auto_increment != 1 && (($cols->not_null && !$cols->has_default) || $cols->primary_key) && is_null($defaultfields["$cols->name"]) && $defaultfields["$cols->name"] == '')
				{
					throwException($tbl.$GLOBALS['lang']['SQL_UPD_FIELD_ERROR'].':'.$cols->name);
				}
				else {
					$set[]= "`".$cols->name."` = ".self::filtrateValue($defaultfields[$cols->name]);
				}
			}
		}
		$set = implode(',',$set);
		#组成更新语句
		$result = "UPDATE $tbl SET $set where $wherefields";
		return $result;
	}

	/**
	 * 插入操作
	 * @param string $tbl    表名
	 * @param array  $a_set  插入字段
	 * @param bool $return_insert_id 是否返回递增id
	 * @return bool
	 */
	public static function insert($tbl, $defaultfields = '', $return_insert_id = false)
	{
		self::$_func = 'insert';
		#组合语句
		$sql = self::getInstance()->buildInsert($tbl, $defaultfields);
		#执行语句操作
		$rs = &self::execute($sql);
		if ($rs !== false)
		{ 
		    $insertId = self::getInsertID();
			if($return_insert_id)
			{
				if(!($dbconn = &DB::getConnection(DB::$_func))) {
				    throwException($GLOBALS['lang']['ADODB_NOT_CONNECT']);
				}
				return $insertId;
			}
			else
			{
				return true;
			}
		}
		else
		{
			throwException($GLOBALS['lang']['SQL_INSERT_ERROR'].$sql);
		}
	}

	/**
	 * 删除操作
	 * @param string $tbl   表名
	 * @param string|array $where 删除条件
	 * @return bool
	 * @access public
	 */
	public static function delete($tbl, $where)
	{
		self::$_func = 'delete';
		#组合语句
		$sql = self::getInstance()->buildDelete($tbl, $where);
		#执行语句操作
		$rs = &self::execute($sql);
		if ($rs)
		{
			return true;
		}
		else
		{
			throwException($GLOBALS['lang']['SQL_DEL_ERROR'].$sql);
			return false;
		}
	}

	/**
	 * 更新操作
	 * @param string $tbl    表名
	 * @param string|array   $set  更新字段
	 * @param string|array   $where  更新限制条件
	 * @return bool
	 */
	public static function update($tbl, $where,$set='')
	{
		self::$_func =  'update';
		#组合语句
		$sql = self::getInstance()->buildUpdate($tbl,$where,$set);
		#执行语句操作
		$rs = &self::execute($sql);
		if ($rs)
		{
			return true;
		}
		else
		{
			throwException($GLOBALS['lang']['SQL_UPD_ERROR'].$sql);
			return false;
		}
	}

	/**
	 * 过滤数据库操作值
	 * @param  $v 需要过滤的值
	 */
	public static function filtrateValue($v)
	{
		if (is_numeric($v) and intval($v) == $v and strlen(intval($v)) === strlen($v))
		{
			$field_values = $v;
		}
		elseif(is_bool($v))
		{
			$field_values = $v;
		}
		elseif(is_null($v))
		{
			$field_values = 'NULL';
		}
		elseif(is_string($v))
		{
			$field_values = "'" .(get_magic_quotes_gpc()?$v:addslashes($v)). "'";
		}
		elseif(is_float($v))
		{
			$field_values = $v;
		}
		else
		{
			$field_values = "'" .serialize($v). "'";
		}
		return $field_values;
	}

	/**
	 * 过滤数据库操作值
	 * @param  $v 需要过滤的值
	 */
	public static function filtrateWhere($v)
	{
		if (is_numeric($v) and intval($v) == $v and strlen(intval($v)) === strlen($v))
		{
			$field_values = $v;
		}
		elseif(is_bool($v))
		{
			$field_values = $v;
		}
		elseif(is_null($v))
		{
			$field_values = 'NULL';
		}
		elseif(is_float($v))
		{
			$field_values = $v;
		}
		elseif(is_string($v))
		{
			$field_values = (get_magic_quotes_gpc()?$v:addslashes($v));
		}
		elseif(is_array($v))
		{
			foreach($v as $k2 => $v2)
			{
				$v[$k2] = self::filtrateWhere($v2);
			}
			$field_values = $v;
		}
		return $field_values;
	}


	/**
	 * 查询操作
	 * @param array $cacheSecs 缓存秒数
	 * @return string
	 * @access public
	 * @static 
	 */
	public static function select($sql,$limit = 0, $offset = 0,$cacheSecs = -1,$arr_col='',$key_col='')
	{
		#设置内存范围
		ini_set('memory_limit', '64M');
		#设置查询范围
		if(is_array($limit))
		{
			extract($limit);
			if(!is_numeric($limit)) {
				$limit = 0;
			}
		}
		#设置查询语句
		self::$_func = 'select';
		$mysqlData = NULL;
		#设置查询范围
		if($limit > 0) {
			$sql .= ' limit ' .(int)$limit;
		}
		if($offset != 0){
			$sql .= ' offset ' .(int)$offset;
		}
		#读取memcache 数据缓存
		if(IS_OPEN_MYSQL_CACHE && $cacheSecs > 0)
		{
			$mcache_key = md5($sql.$key_col);
			#删除缓存
			if(preg_match("'^.+\?del.*$'is",$_SERVER['REQUEST_URI'])) {
				Mcache::delete($mcache_key);
			}
			#获取缓存数据
			if(!preg_match("'^.+\?del.*$'is",$_SERVER['REQUEST_URI']) && ($mysqlData = Mcache::get($mcache_key)) !== false)
			{
				self::$_func = '';
				return $mysqlData;
			}
		}
		#执行查询语句
		$rs = &self::execute($sql,$cacheSecs);
		#错误抛出
		if(!$rs) {
			throwException($GLOBALS['lang']['SQL_SELECT_ERROR'].$sql);
		}
		while(!$rs -> EOF)
		{
			if($limit == -1)
			{
				if(strtoupper($arr_col) == 'CNT')
				{
					if(!($mysqlData = unserialize($rs->fields['CNT'])) && !empty($rs->fields['CNT'])) $mysqlData = explode(',',$rs->fields['CNT']);
				}
				else
				$mysqlData = $rs->fields['CNT'];
			}
			else
			{
				$tmpArr = array_change_key_case($rs->fields, CASE_UPPER);
				if(!empty($arr_col))
				{
					if(!is_array($arr_col)) $arr_col = explode(',',$arr_col);
					foreach($arr_col as $v)
					{
						if(isset($tmpArr[strtoupper($v)]))
						{
							if($tmpVar = unserialize($tmpArr[strtoupper($v)]))
							{
								$tmpArr[strtoupper($v)] = $tmpVar;
							}
							elseif(!empty($tmpArr[strtoupper($v)]))
							{
								$tmpArr[strtoupper($v)] = explode(',',$tmpArr[strtoupper($v)]);
							}
						}
					}
				}
				if(empty($key_col)){
					$mysqlData[] = $tmpArr;
				}
				else{
					$mysqlData[$tmpArr[strtoupper($key_col)]] = $tmpArr;
				}
			}
			adodb_movenext($rs);
		}
		#关键数据库操作
		$rs->close();
		#写入memcache缓存
		if(IS_OPEN_MEMCACHE && $cacheSecs > 0 && !empty($mysqlData)){
			Mcache::set(md5($sql.$key_col),$mysqlData,$cacheSecs);
		}
		return $mysqlData;
	}


	/**
	 * 返回数据总数
	 * @param array $cacheSecs 缓存秒数
	 * @return string
	 * @access public
	 * @static 
	 */
	public static function getRows($tbl,$where = '',$cacheSecs = -1)
	{
		$mysqlData = NULL;
		$sql = "select count(1) CNT from $tbl".(!empty($where)?" where $where ":'');
		$rs = &self::execute($sql,$cacheSecs);
		#错误抛出
		if(!$rs) {
			throwException($GLOBALS['lang']['SQL_SELECT_ERROR'].$sql);
		}
		while ($arr = $rs->FetchRow())
		$mysqlData = $arr['CNT'];
		return $mysqlData;
	}



	/**
	 * 生成数据库查询错误消息
	 * @param object $db
	 * @param string $sql
	 * @return string
	 * @access public
	 * @static 
	 */
	public static function genErrorMsg()
	{
		if(!($dbconn = &DB::getConnection(DB::$_func))) {
			throwException($GLOBALS['lang']['ADODB_NOT_CONNECT'].'['.mysql_errno().'] ');
		}
		$error_msg = "[" . $dbconn->ErrorNo() . "] " . $dbconn->ErrorMsg();
		return $error_msg;
	}

	/**
	 * 返回某个字段最大或最小值
	 * @param string  $tbl 表名
	 * @param string $where 条件
	 * @param string $terminal 类型
	 * @return int
	 * @access public
	 * @static 
	 */
	public static function getFieldTerminal($tbl,$field='id',$where='',$terminal='max',$cacheSecs = -1)
	{
		$mysqlData = false;
		if ($terminal=='max')
		{
			$sql = "SELECT MAX($field) AS terminal FROM $tbl";
		}
		else
		{
			$sql = "SELECT MIN($field) AS terminal FROM $tbl";
		}
		if ($where)
		{
			$sql .= " WHERE  {$where}";
		}
		#执行语句
		$rs = &self::execute($sql,$cacheSecs);
		#错误抛出
		if (!$rs)
		{
			throwException($GLOBALS['lang']['SQL_SELECT_ERROR'].$sql);
		}
		if(!$rs -> EOF) {
			$mysqlData = $rs->fields['terminal'];
		}
		return $mysqlData;
	}

	/**
	 * 执行SQL语句
	 * @return resource set
	 * @access public
	 * @throws Error
	 * @static 
	 */
	public static function &execute($sql,$cacheSecs = -1,$isReConn=false)
	{
		if(empty(self::$_func))
		{
			self::$_func = preg_replace("/^\s*(select|delete|update|insert).+/is","\\1",$sql);
		}
		#错误抛出
		if(!($dbconn = &DB::getConnection(self::$_func,$isReConn))) {
			throwException($GLOBALS['lang']['ADODB_NOT_CONNECT']);
		}
		#判断是否使用缓存
		if(IS_OPEN_MEMCACHE && $cacheSecs >= 0)
		{
			#进行缓存刷新
			if(preg_match("'^.+\?del.*$'is",$_SERVER['REQUEST_URI'])) {
				$dbconn->CacheFlush($sql);
			}
			else {
				$rs = $dbconn->CacheExecute($cacheSecs,$sql);
			}
		}
		else
		{
			#执行查询语句
			$rs = $dbconn->Execute($sql);
			self::$insert_id = null;
			if(self::$_func == 'insert') {
				self::$insert_id = $dbconn->Insert_ID();
			}
			$dbconn->Execute('commit');
			if(!$rs && self::$is_to_404)
			{
				if(!$isReConn && ($dbconn->ErrorNo() == 2006 || $dbconn->ErrorNo() == 0))
				{
					usleep(200000);
					$rs = &self::execute($sql,$cacheSecs,true);
				}
				else{
					throwException($GLOBALS['lang']['SQL_SELECT_ERROR'].'['.$dbconn->ErrorNo().'] '.$sql);
				}
			}
		}
		self::$_func = '';
		#保存执行语句
		array_push(self::$last_query,$sql);
		#判断是否输出语句
		if(self::$is_show_last_query){
			echo '<br/><font color="#FF0000" >',$sql,'</font><br/>';
		}
		return $rs;
	}

	/**
	 * 返回递增id
	 * @param object $db 数据库连接对象
	 * @return int
	 * @access public
	 * @static 
	 */
	public static function getInsertID()
	{
		if(!($dbconn = &DB::getConnection('insert',true))){
			#错误抛出
			throwException($GLOBALS['lang']['ADODB_NOT_CONNECT']);
		}
		$re_id = $dbconn->Insert_ID();
		$re_id = empty($re_id)?mysql_insert_id():$re_id;
		if(!$re_id) {
			$re_id = self::$insert_id;
		}
		return $re_id;
	}

	/**
	 * 输出数据执行语句
	 * @return null
	 */
	public static function echoExeSql(){
		if(!empty(self::$last_query)){
			foreach (self::$last_query as $k){
				echo $k,'<br/>';
			}
		}
	}
 

}
?>